Imports and set up¶

In [12]:
import requests
import numpy as np
import seaborn as sns
import json
import matplotlib.pyplot as plt
import pandas as pd
import holoviews as hv
from holoviews import opts, dim
from bokeh.sampledata.les_mis import data

hv.extension('bokeh')
hv.output(size=200)

I formulate the query to the GraphQL API implemented by the subgraph TetherUSDT: in detail, I want to retrieve the following features with regard to issues, transfers and approvals.

In [18]:
query = """query {
	issues(orderBy: amount, orderDirection: asc) {
    	id
    	amount
 	}
	transfers (first:1000) {
		id
		from	
		to
		value
	}
	approvals {
		id
		owner	
		spender
		value
	}
}"""

I use a temporary query url instead of the real query URL with the generated API key (https://gateway.testnet.thegraph.com/api/e0e91a841b861b1b22da614b4d6ef4b7/subgraphs/id/EW29ZpxgJRNUzjW5qs1KRyijWh2t171WmgNJ8sNmN7P5), since the latter method requires the adding of some GRT to my billing balance.

In [19]:
url = "https://api.studio.thegraph.com/query/16935/tetherusdt/v0.0.3"
r = requests.post(url, json={'query': query})
print(r.status_code)
#print(r.text)
200
In [20]:
json_data = json.loads(r.text)

Issues¶

In [21]:
df_issues = pd.DataFrame(json_data["data"]["issues"])
df_issues['amount'] = pd.to_numeric(df_issues['amount'])

The first 5 records of issues DataFrame.

In [22]:
df_issues.head()
Out[22]:
id amount
0 0x2ec30181b26f842558280b682a5a58e15cae6498b4c1... 8005642000
1 0x8cfc4f5f4729423f59dd1d263ead2f824b3f133b02b9... 10000000000
2 0x27b0df3879a34fff8abd827eb0a05892041af7763a88... 10000000000000
3 0x31f01e3f69d763c70e9965c370475f454338effdcca4... 15000000000000
4 0x79595df1a5d8b96c017cd78bbd844fb68c94c19edb55... 15000000000000
In [23]:
values = list(df_issues['amount'].value_counts().sort_index().keys())
frequencies = df_issues['amount'].value_counts().sort_index()
In [39]:
plt.figure(figsize=(35,10))
ax = sns.barplot(x=values, y=frequencies)
ax.set_xticklabels(ax.get_xticklabels(),rotation = 30)
xlabels = [j//1000000000 for j in values]
ax.set_xticklabels(xlabels)
plt.title("Bar plot of the frequencies of issues amounts (in billion)")
sns.set(font_scale = 2)
plt.show()

Approvals¶

In [40]:
df_approvals = pd.DataFrame(json_data["data"]["approvals"])
In [41]:
df_approvals.head()
Out[41]:
id owner spender value
0 0x00006c3fc1b710ac8519fc19b568a641ef3ce1f51aa0... 0xf9b42f4c01c51847d16de067a62dc823487dc18f 0x0eee3e3828a45f7601d5f54bf49bb01d1a9df5ea 1157920892373161954235709850086879078532699846...
1 0x0000b23d81498f6665dfed904fd29eaea290874ff852... 0xf0fce8c7029175f04ec789fe30a77b7731832bfc 0x818e6fecd516ecc3849daf6845e3ec868087b755 5789604461865809771178549250434395392663499233...
2 0x0000dc41a87775f20fb724cea53f1d4263df2c1834cb... 0x806ff9f29d137c6cdbf3f16f30396123aa567a20 0x2faf487a4414fe77e2327f0bf4ae2a264a776ad2 1157920892373161954235709850086879078532699846...
3 0x0001011e6128b3dfa3b9c47465250d9c17b1e3f1cd95... 0x6a2abcce75788b2565bfdb5065168f2c5912c19b 0xf650c3d88d12db855b8bf7d11be6c55a4e07dcc9 1157920892373161954235709850086879078532699846...
4 0x00016199dfbf0b0950f1fd95e0a2ed082776b78c5243... 0x4166f82410b139a2f7b1a28b07b823b6f2ce92db 0x41f8d14c9475444f30a80431c68cf24dc9a8369a 1157920892373161954235709850086879078532699846...

The value feature assumes incredibly high number.

In [42]:
df_approvals['value'][0]
Out[42]:
'115792089237316195423570985008687907853269984665640564039457584007913129639935'

In order to plot these values, I try to manage their magnitude through standardization (min-max scaling), however I discover that the best approach is approximate each amount with the number of its digits.

In [43]:
values_length = [len(i) for i in df_approvals['value']]
In [44]:
plt.figure(figsize=(30,6))
sns.histplot(values_length, discrete=True)
plt.title("Frequency distribution of values' length")
sns.set(font_scale = 0.5)
plt.show()
In [45]:
df_approvals['value'] = values_length
In [46]:
graph = hv.Sankey(df_approvals.iloc[:, 1:])
graph.opts(
    opts.Sankey(label_position='left', width=600, height=1100, cmap='Set1',
                edge_color=dim('spender').str(), node_color=dim('spender').str()))
Out[46]:

Transfers¶

In [47]:
df_transfers = pd.DataFrame(json_data["data"]["transfers"])
df_transfers['value'] = pd.to_numeric(df_transfers['value'])

The first 5 records of transfers DataFrame.

In [48]:
df_transfers.head()
Out[48]:
id from to value
0 0x0000000fc33284d2faf6050890764158f09a838db415... 0x5694d5d33c810d7929a385ff936217f07ecd046f 0x358f78edabf6556d3d84b3758110a77b30b2868b 1800000000
1 0x0000004344abcb2643843225b031a4bdb2c6b6a69ba0... 0xfe7e6564a8b820e2e7363c5460edec4cc7743faa 0xa5407eae9ba41422680e2e00537571bcc53efbfd 0
2 0x000000ae8958e6020b8d6bd7c57ec675f1200188f56e... 0xcd3d365c3c8f158a5ad4fd4a5659b8a1f9b0d03d 0x1062a747393198f70f71ec65a582423dba7e5ab3 122541997
3 0x00000112ed5c1d8741bb77c2b0920758ea29bc1c567a... 0xcf3618d4680817af786a1d93465a19ab4225e69e 0xd331227a7fe6682a93e8fa07700779f11996dd3a 527000000
4 0x000001583813f32c6badcf580cabf37a20ba717e48ac... 0xd70177a0a6b5b1cb0ad586d419b693cfa631b097 0x818157b54809eec4f36b56e752cd695dba11d50d 987000000
In [51]:
df_transfers['from'].value_counts()[:15]
Out[51]:
0x1062a747393198f70f71ec65a582423dba7e5ab3    29
0xfdb16996831753d5331ff813c29a93c76834a0ad    28
0xadb2b42f6bd96f5c65920b9ac88619dce4166f94    19
0xab5c66752a9e8167967685f1450532fb96d5d24f    18
0xeee28d484628d41a82d01e21d12e2e78d69920da    17
0x46705dfff24256421a05d056c29e81bdc09723b8    16
0x0a98fb70939162725ae66e626fe4b52cff62c2e5    15
0x6748f50f686bfbca6fe8ad62b22228b87f31ff2b    14
0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be    12
0x5041ed759dd4afc3a72b8192c143f72f4724081a    11
0xe93381fb4c4f14bda253907b18fad305d799241a    11
0xd551234ae421e3bcba99a0da6d736074f22192ff     9
0xc6bd3edd07e294cb66b8318356d688b3516ea950     8
0x0d0707963952f2fba59dd06f2b425ace40b492fe     7
0x035cb93d40a60d9adcd0a38954e802720996b481     6
Name: from, dtype: int64
In [52]:
df_transfers['to'].value_counts()[:15]
Out[52]:
0x6748f50f686bfbca6fe8ad62b22228b87f31ff2b    12
0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be    12
0xab5c66752a9e8167967685f1450532fb96d5d24f     9
0xeee28d484628d41a82d01e21d12e2e78d69920da     9
0x46705dfff24256421a05d056c29e81bdc09723b8     8
0x1062a747393198f70f71ec65a582423dba7e5ab3     8
0xfdb16996831753d5331ff813c29a93c76834a0ad     7
0xadb2b42f6bd96f5c65920b9ac88619dce4166f94     7
0xe93381fb4c4f14bda253907b18fad305d799241a     7
0x5041ed759dd4afc3a72b8192c143f72f4724081a     7
0x6cc5f688a315f3dc28a7781717a9a798a59fda7b     7
0x0a98fb70939162725ae66e626fe4b52cff62c2e5     7
0x75e89d5979e4f6fba9f97c104c2f0afb3f1dcb88     6
0x4c3dc6d5d3fc47060b5644d057aeb12d31fa79ee     6
0x0d4a11d5eeaac28ec3f61d100daf4d40471f1852     4
Name: to, dtype: int64
In [56]:
chord = hv.Chord(df_transfers.iloc[:, 1:])
chord.opts(
	opts.Chord(cmap='Set1', edge_cmap='Set1', edge_color='from', labels='from', node_color='from', width=600, height=600))
Out[56]:
In [55]:
chord = hv.Chord(df_transfers.iloc[:100, 1:])
chord.opts(
	opts.Chord(cmap='Set1', edge_cmap='Set1', edge_color='from', labels='from', node_color='from', width=600, height=600))
Out[55]:

The GraphQL query language presents high flexibility regarding query constructs: for instance, I can query a subset of the available features of transfers filtering by a specific source address.

In [58]:
query_filtered = """query {
	transfers(
		where: {
			from: "0xfdb16996831753d5331ff813c29a93c76834a0ad"},
		orderBy: value,
		orderDirection: desc
	)  {
		from	
		to
		value
	}
}"""

r = requests.post(url, json={'query': query_filtered})
print(r.status_code)
#print(r.text)
json_data = json.loads(r.text)
df_transfers_filtered = pd.DataFrame(json_data["data"]["transfers"])
200
In [59]:
df_transfers_filtered
Out[59]:
from to value
0 0xfdb16996831753d5331ff813c29a93c76834a0ad 0xab5c66752a9e8167967685f1450532fb96d5d24f 32619254448700
1 0xfdb16996831753d5331ff813c29a93c76834a0ad 0x418b9d7fd9b3ded3c23668a60865952accd0096e 10000000000000
2 0xfdb16996831753d5331ff813c29a93c76834a0ad 0xe71d51b03015d0ba6158a4dca6e56256c5020a1d 4000000000000
3 0xfdb16996831753d5331ff813c29a93c76834a0ad 0xe71d51b03015d0ba6158a4dca6e56256c5020a1d 4000000000000
4 0xfdb16996831753d5331ff813c29a93c76834a0ad 0x069d76ffd7906e4ab4307c1287282a8a9247e938 2899999000000
... ... ... ...
95 0xfdb16996831753d5331ff813c29a93c76834a0ad 0x79115db24062f17d53f52557845d1d61f12c8272 1000000000000
96 0xfdb16996831753d5331ff813c29a93c76834a0ad 0x80cfe8be400ded3a2bc23bbf760f52546a976029 1000000000000
97 0xfdb16996831753d5331ff813c29a93c76834a0ad 0xc148955b92475df692f0bfb01a494248d1a6b75b 1000000000000
98 0xfdb16996831753d5331ff813c29a93c76834a0ad 0x44f3092a2dd628df99988db65fa20e19586797f5 1000000000000
99 0xfdb16996831753d5331ff813c29a93c76834a0ad 0x29899ef9d9430c0917644d407ef8fe72a3ee4c62 1000000000000

100 rows × 3 columns